Databases are used to store large amounts of data and make such data accessible to one or more users. A typical database configuration is to have one or more servers in communications with a database, which may be stored in any type of data storage device, and one or more clients. The clients request the data by way of a query. A query specifies the type of data desired and operates much like a search. For example, in a database containing information pertaining to a business' customer list, a client may desire the names and addresses of all customers that have purchased a particular product. Thus, a query may specify the type of data requested (customer name and address) and the criteria for selection (the product). The server finds the data entries having the purchased product, and returns the customer name and address information.
In many business, academic and other environments, the amount of data stored in a database may be extremely large. To accommodate such data, databases may be organized in a number of different ways. Two significant types of databases are relational and analytical databases. A relational database takes the form of tables having rows and columns that show the relationships between items in the database. In the customer list example discussed above, for example, customer data may be located in the rows of the table, and the product information may be located in the columns. Multiple tables may be related to each other, and an item change in one table affects other, related tables. One language that can used to query and process data in a relational database is Structured Query Language (SQL).
Relational databases typically enable a “prepare” operation that does much of the upfront work that is carried out on the server before the server can begin sending results back to the client. The prepare operation includes validating the query sent by the user and creating an execution plan that determines the most efficient way to execute the query. The prepare operation is useful if the same query is to be executed multiple times (possibly with different parameters for each query), thereby allowing the upfront preparation costs to be amortized over those requests.
Another type of database is an analytical, or multidimensional, database. Rather than having tables of two-dimensional data, as in a relational database, an analytical database contains data that may be represented as three-dimensional data “cubes.” Thus, an analytical database provides for more complex relationships between data items. An analytical database is also optimized for data warehousing and Online Analytical Processing (OLAP) applications. An analytical database can, in some instances, be created by using a relational database as input. In contrast to the SQL queries used to interact with a relational database, however, analytical databases can be accessed with Multidimensional Expressions (MDX), which can be implemented using extensible Markup Language for Analysis (XMLA). MDX syntax permits a user of an analytical database to perform functions with the data that are more sophisticated than those available to users of relational databases. For example, a high-level database user such as a strategic planner for a business may wish to perform a “what-if” analysis of the data. In such an analysis, the user queries the database to request a certain selection of the data and the data is analyzed to determine the effects of possible changes in market conditions, business decisions, etc.
In conventional analytical database systems, the query results are sent to the client requestor and then immediately cleared from the server. The immediate clearing of the data from the server is performed to increase efficiency. For example, an analytical database, containing highly-complex three-dimensional data as discussed above, may be extremely large, and there may be many different clients of the server that are concurrently submitting query requests. The results of such queries may also be very large. Thus, a design tradeoff reduces the time and resources that are required to service numerous requests from multiple clients on the often vast amount of stored data by eliminating query results as soon as they are sent to a requesting client.
In some situations, however, it would be advantageous to prepare a query execution plan, as is possible in relational database systems. It would be even more advantageous to be able to store query results in an analytical database environment because the types of sophisticated queries that are performed on an analytical database typically lead to follow-up queries, additional analysis of previous query results, and so forth. For example, a user who performs a what-if analysis will typically want to ask follow-up what-if type questions to see if or how the results might change. In addition, one or more “writeback” operations may be desired during such a process.
For example, a user may query the analytical database to perform a what-if scenario. A what-if scenario typically involves changing one or more query parameters and then determining what occurs as a result. For example, an initial what-if scenario for a store might be to query a sales database to predict the increase in sales that could result from sending 10% discount coupons to consumers in a particular geographic area. A follow-up what-if scenario could be to predict the sales increase that might result from sending 20% coupons, or from sending 10% coupons to a different geographic area, and so forth. Upon viewing the results, the user may want to know more about the scenario by performing additional queries and/or functions on the data as it exists in the what-if scenario. For example, the follow-up what-if scenario could assume that the 10% coupons were sent, and then assume additional factors using the previously-determined query results. Thus, the additional queries and/or functions are performed on the query result data that is written back as though the result data is the “real” data stored in the analytical database. As the result data is only used for purposes of the user's what-if scenario, it can be seen that such a writeback operation should ideally be performed without actually overwriting the data stored in the database unless the user specifically desires to overwrite such data.
In conventional analytical database environments, the XMLA standard describes Simple Object Access Protocol (SOAP) header XML elements that initiate a stateful session between a client and an analytical database server. However, the standard currently lacks a mechanism to enable a client to request the server to execute a query, store the query results in server memory, and also dynamically update the in-memory stored result due to writeback operations. In light of the design tradeoff noted above, enabling such a mechanism would be counter-intuitive because it would force the server to take additional processing time and resources to store query results in memory and then permit client interaction with the query results. Nevertheless, the ability to perform such follow-up querying and writeback operations make the storage of query results highly desirable in such limited instances.
As noted above, relational databases enable the preparation of a query execution plan, which optimizes the query process for multiple queries by first planning out an optimal query methodology. As was also noted above, conventional analytical database environments lack the ability to store query results, and therefore such environments do not enable the optimization of queries. For example, neither OLAP nor XMLA provide a mechanism for the “prepare” and “query execution plan” features that are available in conventional relational database environments. Such features would be highly desirable in an analytical database environment because of the highly complex queries that are typically involved.
Furthermore, the ability to have an analytical database server store query results would enable the server to return portions of the results to a requesting client. For example, users of analytical databases are conventionally unable to receive parts of a query result at a time. Instead, such users typically have to receive the entire result and store the result in local memory and/or storage, and display the appropriate parts of the data. Alternatively, the client reads only the amount of data that can be displayed and blocks the network connection until the client can discard the current batch of data and fetch the next block of data.
Accordingly, what is needed is a mechanism to enable the temporary storage of query results in an analytical database environment. More particularly, what is needed is a mechanism for enabling true “what-if” scenario testing, while keeping permanently stored data insulated from any of the effects of an attendant writeback operation. Even more particularly, what is needed is a mechanism to enable the preparation of a query execution plan for one or more intended queries of an analytical database. Furthermore, what is needed is a mechanism that enables an analytical database server to store a query result and transmit parts of the result to the requesting client